#!/usr/bin/env python
# -*- coding: UTF-8 -*-
from typing import List, Tuple, Optional
from sqlalchemy import asc, func, and_
from .base_dao import getDatabaseSession
from app.dao.models.mysql_gen import Project


class ProjectQueryDao(object):
    """项目查询类dao"""

    @classmethod
    def get_by_id(cls, project_id: str) -> Optional[Project]:
        """根据ID查询项目"""
        with getDatabaseSession() as session:
            query = session.query(Project).filter(Project.id == project_id)
            result = query.first()
            return result

    @classmethod
    def list_projects(cls, page: int = 1, page_size: int = 10, **kwargs) -> Tuple[int, List[Project]]:
        """分页查询项目列表"""
        with getDatabaseSession() as session:
            query = session.query(Project)
            
            # 添加过滤条件
            if kwargs.get('project_number'):
                query = query.filter(Project.project_number.like(f"%{kwargs['project_number']}%"))
            if kwargs.get('project_type'):
                query = query.filter(Project.project_type == kwargs['project_type'])
            if kwargs.get('status'):
                query = query.filter(Project.status == kwargs['status'])
            
            # 获取总数
            total = query.count()
            
            # 添加分页和排序
            result = query.order_by(asc(Project.created_at)).offset((page - 1) * page_size).limit(page_size).all()
            
            return total, result


class ProjectOperateDao(object):
    """操作项目相关dao"""

    @classmethod
    def create_project(cls, project: Project) -> Project:
        """创建项目"""
        with getDatabaseSession(False) as session:
            session.add(project)
            session.commit()
            session.refresh(project)
        return project

    @classmethod
    def update_project(cls, project_id: str, **kwargs) -> bool:
        """更新项目"""
        with getDatabaseSession() as session:
            # 过滤掉None值
            update_data = {k: v for k, v in kwargs.items() if v is not None}
            if not update_data:
                return False
            
            result = session.query(Project).filter(Project.id == project_id).update(update_data)
            session.commit()
            return result > 0

    @classmethod
    def delete_project(cls, project_id: str) -> bool:
        """删除项目"""
        with getDatabaseSession() as session:
            result = session.query(Project).filter(Project.id == project_id).delete()
            session.commit()
            return result > 0